using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
namespace XProject.DAO
{
    class IconDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(IconID) from [Icon]";
        string SQL_SELECT_ALL = "select * from [Icon]";
        string SQL_SELECT_BY_ID = "select * from [Icon] where IconID=@iconId";
        string SQL_SELECT_BY_KEY = "select * from [Icon] where Description=@key";
        string SQL_INSERT = "insert into [Icon]([Image], [Description]) values(@image, @description)";
        string SQL_UPDATE = "update [Icon] set [Image]=@image, [Description]=@description where IconID= @iconID";
        string SQL_DELETE = "delete from [Icon] where IconID=@iconID";
        #endregion
        #region fields
        string message="";
        #endregion
        #region properties
        public string Message
        {
            get{return message;}
        }
        #endregion
        #region method
        public IList<Entity.FeelingIcon> GetAll()
        {
            IList<Entity.FeelingIcon> list = new List<Entity.FeelingIcon>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Entity.FeelingIcon icon = new XProject.Entity.FeelingIcon();
                        icon.IconID = dr.GetInt32(0);
                        icon.ImagePath = dr.GetString(1);
                        icon.Description = dr.GetString(2);
                        list.Add(icon);
                    }
                    Disconnect();
                }

            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return list;
        }
        public Entity.FeelingIcon GetIcon(int iconId)
        {
            Entity.FeelingIcon icon = new XProject.Entity.FeelingIcon();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_ID, conn);
                command.Parameters.Add("@iconId", OleDbType.Integer).Value = iconId;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    if (dr.Read())
                    {                       
                        icon.IconID = dr.GetInt32(0);
                        icon.ImagePath = dr.GetString(1);
                        icon.Description = dr.GetString(2);             
                    }
                    Disconnect();
                }

            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return icon;
        }
        public Entity.FeelingIcon GetIconByName(string key)
        {
            Entity.FeelingIcon icon = new XProject.Entity.FeelingIcon();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_KEY, conn);
                command.Parameters.Add("@key", OleDbType.VarChar).Value = key;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    
                    if (dr.Read())
                    {
                        icon.IconID = dr.GetInt32(0);
                        icon.ImagePath = dr.GetString(1);
                        icon.Description = dr.GetString(2);
                    }
                    Disconnect();
                }

            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return icon;
        }
        public int Insert(Entity.FeelingIcon icon)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@image", OleDbType.VarChar).Value = icon.ImagePath;
                command.Parameters.Add("@description", OleDbType.VarChar).Value = icon.Description;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    icon.IconID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Update(Entity.FeelingIcon icon)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
                command.Parameters.Add("@image", OleDbType.VarChar).Value = icon.ImagePath;
                command.Parameters.Add("@description", OleDbType.VarChar).Value = icon.Description;
                command.Parameters.Add("@iconID", OleDbType.Integer).Value = icon.IconID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                   
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Delete(Entity.FeelingIcon icon)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);                
                command.Parameters.Add("@iconID", OleDbType.Integer).Value = icon.IconID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        #endregion
    }
}
